package globalCode;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

//Analyzes the connection between two specific users - usually the innermost loop, so any slowdowns here compound quickly

public class UserConnection implements Comparable<UserConnection>{
	Connection con; //Connection to the database
	public int id1; //The first user in the pair
	public int id2; //The second user
	boolean friends = false; //Are the users friends?
	public int wallPosts = 0, numGroups = 0, numThreads = 0, pageComments = 0; //How many of these connections do the users share?
	Integer[] sharedGroups = new Integer[0]; //The list of groups shared by the users
	long oldTime = 0; //Used for debugging how long this is taking
	long newTime = 0; //Ditto
	boolean analyzed = false; //Has the analysis already been run once?

	public UserConnection(int idA, int idB, Connection c){
		id1 = idA;
		id2 = idB;
		con = c;
		analyze();
	}

	public UserConnection() {}

	//Check each individual type of connection between the users
	public void analyze(){
		Statement stmt = null;
		ResultSet rs = null;
		timeDiff(); //How long since this function has last been run -- used in debug
		try {
			//System.out.println("a "+timeDiff());
			stmt = con.createStatement();
			stmt.executeQuery("USE inetwork");
			//System.out.println("b "+timeDiff());
			
			//Check if the two users are friends
			rs = stmt.executeQuery("SELECT * FROM usr_friend WHERE (usr1="+id1+" AND usr2="+id2+") OR (usr1="+id2+" AND usr2="+id1+")");
			rs.last();
			if(rs.getRow()==1)
				friends = true;
			
			//System.out.println("c "+timeDiff());
			
			wallPosts = numWallPosts(id1, id2, stmt);
			//System.out.println("d "+timeDiff());
			numGroups = numGroups(id1, id2, stmt, rs);
			//System.out.println("numGroups: "+numGroups);
			//System.out.println("e "+timeDiff());
			numThreads = numThreads(id1, id2, stmt, rs);
			//System.out.println("f "+timeDiff());
			pageComments = numPageComments(id1, id2, stmt, rs);
			//System.out.println("g "+timeDiff());
			analyzed = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			
			//Clears the connection info if it wasn't already
			if(rs!=null){
				try{
					rs.close();
				} catch(SQLException sqlEx){}

				rs = null;
			}

			if(stmt!=null){
				try{
					stmt.close();
				} catch(SQLException sqlEx){}

				stmt = null;
			}
		}
	}

	//Not "are these the same two users", but "do they have the same score?" Used for ranking
	@Override
	public int compareTo(UserConnection other) {
		if(this.value()!=other.value())
			return other.value()-this.value();
		else
			return 1;
	}

	public int numWallPosts(int i1, int i2, Statement st){
		try{
			ResultSet rs;
			
			//Compound query to determing how many posts on each others walls were made by the other
			rs = st.executeQuery("SELECT * FROM blocktype_wall_post WHERE (`from` ="+id1+" AND `instance` IN " +
					"(SELECT block_instance.id FROM `view` INNER JOIN block_instance ON (block_instance.view=view.id) WHERE view.owner="+id2+" AND block_instance.blocktype='wall')) " +
					"OR (`from` ="+id2+" AND `instance` IN " +
					"(SELECT block_instance.id FROM `view` INNER JOIN block_instance ON (block_instance.view=view.id) WHERE view.owner="+id1+" AND block_instance.blocktype='wall'));");
			rs.last();
			return rs.getRow();
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		return 0;
	}
	
	//How many comments did a user make on a page another owns?
	public int numPageComments(int i1, int i2, Statement st, ResultSet r){
		int num = 0;
		try{
 
			r = st.executeQuery("SELECT `artefact`.`id` FROM `artefact` JOIN `artefact_comment_comment` ON `artefact`.`id` = `artefact_comment_comment`.`artefact` " +
					"WHERE `artefact_comment_comment`.`deletedby` IS NULL AND `artefact`.`artefacttype`='comment' AND `artefact`.`author`="+id1+" AND `artefact`.`owner`="+id2);
			r.last();
			num+=r.getRow();
			r = st.executeQuery("SELECT `artefact`.`id` FROM `artefact` JOIN `artefact_comment_comment` ON `artefact`.`id` = `artefact_comment_comment`.`artefact` " +
					"WHERE `artefact_comment_comment`.`deletedby` IS NULL AND `artefact`.`artefacttype`='comment' AND `artefact`.`author`="+id2+" AND `artefact`.`owner`="+id1);
			r.last();
			num+=r.getRow();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return num;
	}

	//How many groups are both users in?
	public int numGroups(int i1, int i2, Statement st, ResultSet r){

		int num = 0; //Number of shared groups
		ArrayList<Integer> sharedList = new ArrayList<Integer>();
		try {
			
			//Don't get repeated groups, any groups that both are a member of.
			r = st.executeQuery("SELECT DISTINCT `group` FROM group_member WHERE `group` IN " +
					"(SELECT `group` FROM group_member WHERE `member`="+i1+") AND `member`="+i2);
			Object[] groupObjs = resultsList(r, 1);
			int[] groupList = new int[groupObjs.length];
			for(int k = 0; k<groupList.length; k++){
				num++;
				long l = ((Long)groupObjs[k]);
				sharedList.add((int)l);
			}
			sharedGroups = sharedList.toArray(new Integer[0]);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return num;
	}

	//How many forum threads did both users post in? This can be a slow function, depending.
	public int numThreads(int i1, int i2, Statement st, ResultSet r){
		int sharedThreads = 0;
		//long o = System.currentTimeMillis();
		try {
			//All groups that they share
			ArrayList<Integer> allForums = new ArrayList<Integer>();
			for(int i = 0; i<sharedGroups.length; i++){
				int groupIndex = sharedGroups[i];
				allForums.add(groupIndex);
			}
			
			if(allForums.size()>0){
				
				//Get all the threads that the users _could_ have posted in.
				String forumQuery = "SELECT `id` FROM interaction_forum_topic WHERE ";
				for(int i = 0; i<allForums.size(); i++){
					if(i==0)
						forumQuery += "(";
					forumQuery +="`forum`="+allForums.get(i);
					if(i<allForums.size()-1)
						forumQuery += " OR ";
					else
						forumQuery += ")";
				}

				r = st.executeQuery(forumQuery);
				Object[] threadObjs = resultsList(r, 1);

				int[] threadList = new int[threadObjs.length];
				for(int j = 0; j<threadList.length; j++){
					long l = ((Long)threadObjs[j]);
					threadList[j] = (int)l;
				}
				
				if(threadList.length>0){
					//Get all of those that the first user posted in
					String postQuery = "SELECT `topic` FROM interaction_forum_post WHERE ";
					for(int j = 0; j<threadList.length; j++){
						if(j==0)
							postQuery += "(";
						postQuery +="`topic`="+threadList[j];
						if(j<threadList.length-1)
							postQuery += " OR ";
						else
							postQuery += ") AND";
					}
					postQuery += "`poster`="+id1+" GROUP BY topic";

					ResultSet postResults = st.executeQuery(postQuery);
					Object[] postObjs = resultsList(postResults, 1);
					int[] postList = new int[postObjs.length];
					for(int k = 0; k<postList.length; k++){
						long l = ((Long)postObjs[k]);
						postList[k] = (int)l;
					}
					
					if(postList.length>0){
						//Get all of those that the second user ALSO posted in
						String threadsQuery = "SELECT topic FROM interaction_forum_post WHERE ";
						Set<Integer> postSet = new HashSet<Integer>();
						for(int k = 0; k<postList.length; k++)
							if(postSet.add(postList[k])){
								if(k==0)
									threadsQuery+="(";
								threadsQuery +="`topic`="+postList[k]+" OR ";
							}
						if(postSet.size()>0){
							threadsQuery = threadsQuery.substring(0, threadsQuery.length()-4);
							threadsQuery += ") AND ";
						}
						threadsQuery += "`poster`="+id2+" GROUP BY topic";
						//System.out.println(threadsQuery);
						r = st.executeQuery(threadsQuery);
						r.last();
						sharedThreads+=r.getRow();
					}

				}
			}


		} catch(SQLException e){
			e.printStackTrace();
		}

		return sharedThreads;
	}
	
	//Completely unused at the moment, an alternate way of deriving the number of shared threads
	public int numThreads2(int i1, int i2, Statement st, ResultSet r){
		long o = System.currentTimeMillis();
		int sharedThreads = 0;
		try {
			String forumQuery = "SELECT `id` FROM interaction_forum_topic WHERE `forum` IN " +
					"(SELECT `id` FROM interaction_instance WHERE `group` IN " +
					"(SELECT DISTINCT `group` FROM group_member WHERE `group` IN " +
					"(SELECT `group` FROM group_member WHERE `member`="+i1+") AND `member`="+i2+"))";

			r = st.executeQuery(forumQuery);
			Object[] threadObjs = resultsList(r, 1);

			int[] threadList = new int[threadObjs.length];
			for(int j = 0; j<threadList.length; j++){
				long l = ((Long)threadObjs[j]);
				threadList[j] = (int)l;
			}
			if(threadList.length>0){
				String postQuery = "SELECT `topic` FROM interaction_forum_post WHERE ";
				for(int j = 0; j<threadList.length; j++){
					if(j==0)
						postQuery += "(";
					postQuery +="`topic`="+threadList[j];
					if(j<threadList.length-1)
						postQuery += " OR ";
					else
						postQuery += ") AND";
				}
				postQuery += "`poster`="+id1+" GROUP BY topic";

				ResultSet postResults = st.executeQuery(postQuery);
				Object[] postObjs = resultsList(postResults, 1);
				int[] postList = new int[postObjs.length];
				for(int k = 0; k<postList.length; k++){
					long l = ((Long)postObjs[k]);
					postList[k] = (int)l;
				}
				if(postList.length>0){
					String threadsQuery = "SELECT topic FROM interaction_forum_post WHERE ";
					Set<Integer> postSet = new HashSet<Integer>();
					for(int k = 0; k<postList.length; k++)
						if(postSet.add(postList[k])){
							if(k==0)
								threadsQuery+="(";
							threadsQuery +="`topic`="+postList[k]+" OR ";
						}
					if(postSet.size()>0){
						threadsQuery = threadsQuery.substring(0, threadsQuery.length()-4);
						threadsQuery += ") AND ";
					}
					threadsQuery += "`poster`="+id2+" GROUP BY topic";
					//System.out.println(threadsQuery);
					r = st.executeQuery(threadsQuery);
					r.last();
					sharedThreads+=r.getRow();
				}

			}
			System.out.println("numGroupsTime: "+(System.currentTimeMillis()-o));


		} catch(SQLException e){
			e.printStackTrace();
		}
		return sharedThreads;
	}
	
	
	//Utility function converting an sql result set into an object array
	public Object[] resultsList(ResultSet rs, int column){
		Object[] toReturn = new Object[0];
		try{
			rs.last();
			int end = rs.getRow();
			rs.beforeFirst();
			toReturn = new Object[end];
			for(int i = 0; i< end; i++){
				rs.next();
				toReturn[i] = rs.getObject(column);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}

		return toReturn;
	}

	//Utility and debug function used to find out how long certain functions are taking to run
	private long timeDiff() {
		oldTime = newTime;
		newTime = System.currentTimeMillis();
		return newTime-oldTime;
	}

	//A text version of the output, mainly used for debugging at this point
	@Override
	public String toString(){
		String s = "";
		if(friends)
			s+=id1+" and "+id2+" are friends\n";
		else
			s+=id1+" and "+id2+" aren't friends\n";
		s+="They have "+wallPosts+" mutual wall post(s)\n";
		s+="They have "+pageComments+" mutual page comment(s)\n";
		s+="They share "+numGroups+" group(s)\n";
		s+="They have posted in "+numThreads+" of the same forum thread(s)\n";
		return s;	
	}

	//Arbitrary means of "scoring" two users based on their connections, will continue to alter based on data
	public int value(){
		int val = 0;
		if(friends)
			val+=5;
		val+=wallPosts*2;
		val+=pageComments*2;
		val+=numGroups;
		val+=numThreads/2;
		return val;
	}

	//returns all of the connection information in array form, used for insertion into the database as well as JTable display
	public Object[] toInfoArray() {
		if(!analyzed)
			analyze();
		Object[] info = new Object[8];
		info[0] = this.id1;
		info[1] = this.id2;
		info[2] = (this.friends) ? 1:0;
		info[3] = this.wallPosts;
		info[4] = this.pageComments;
		info[5] = this.numGroups;
		info[6] = this.numThreads;
		info[7] = this.value();
		return info;
	}
}
